import requests
import pymysql
import time, datetime
import json
import hashlib
import traceback
import sys
from bs4 import BeautifulSoup
import re
from config import *

def get_conn():
    """
    :return: 连接，游标
    """
    # 创建连接
    conn = pymysql.connect(host=HOST,
                           user=USER,
                           password=PASSWORD,
                           db=DATABASE,
                           charset="utf8")
    # 创建游标
    cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
    return conn, cursor


def close_conn(conn, cursor):
    cursor.close()
    conn.close()


def query(sql, *args):
    """
    封装通用查询
    :param sql:
    :param args:
    :return: 返回查询到的结果，((),(),)的形式
    """
    conn, cursor = get_conn()
    cursor.execute(sql, args)
    res = cursor.fetchall()
    close_conn(conn, cursor)
    return res


def get_world_data():
    """
    :return: 返回海外疫情数据
    """
    url_world = 'https://api.inews.qq.com/newsqa/v1/automation/modules/list?modules=FAutoCountryConfirmAdd,WomWorld,WomAboard'
    headers = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.88 Safari/537.36',
    }
    r_world = requests.get(url_world, headers)
    res_world = json.loads(r_world.text)
    data_world = res_world['data']['WomAboard']
    world_details = {}
    for i in data_world:
        temp = i['y'] + '.' + i['date']
        tup = time.strptime(temp, '%Y.%m.%d')
        dt = time.strftime('%Y-%m-%d', tup)  # 改变时间格式，插入数据库 日期
        # print(ds)
        c_name = i['name']  # 国家
        continent = i['continent']  # 所属大洲
        nowConfirm = i['nowConfirm']  # 现有确诊
        confirm = i['confirm']  # 累计确诊
        confirmAdd = i['confirmAdd']  # 新增确诊
        suspect = i['suspect']  # 现有疑似
        heal = i['heal']  # 累计治愈
        dead = i['dead']  # 累计死亡
        confirmAddCut = i['confirmAddCut']
        confirmCompare = i['confirmCompare']
        nowConfirmCompare = i['nowConfirmCompare']
        healCompare = i['healCompare']
        deadCompare = i['deadCompare']
        world_details[c_name] = {'dt': dt,
                                 'continent': continent,
                                 'nowConfirm': nowConfirm,
                                 'confirm': confirm,
                                 'confirmAdd': confirmAdd,
                                 'suspect': suspect,
                                 'heal': heal,
                                 'dead': dead,
                                 'confirmAddCut': confirmAddCut,
                                 'confirmCompare': confirmCompare,
                                 'nowConfirmCompare': nowConfirmCompare,
                                 'healCompare': healCompare,
                                 'deadCompare': deadCompare,
                                 }
    # print(world_details)
    return world_details


def get_map_data():
    url_det = 'https://api.inews.qq.com/newsqa/v1/query/inner/publish/modules/list?modules=diseaseh5Shelf'
    headers = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) '
                      'Chrome/79.0.3945.88 Safari/537.36',
    }
    r_det = requests.get(url_det, headers)
    res_det = json.loads(r_det.text)
    data_det = res_det['data']['diseaseh5Shelf']

    details_province = []  # 当日详细数据

    update_time = data_det["lastUpdateTime"]
    data_country = data_det["areaTree"]  # list 之前有25个国家,现在只有中国
    data_province = data_country[0]["children"]  # 中国各省

    for pro_infos in data_province:
        province = pro_infos["name"]  # 省名
        confirm = pro_infos["total"]["confirm"]  # 累计确诊
        confirm_add = pro_infos["today"]["local_confirm_add"]  # 本土新增确诊
        wzz_add = pro_infos["today"]["wzz_add"]  # 本土无症状
        confirm_now = pro_infos["total"]["nowConfirm"]  # 现有确诊
        heal = pro_infos["total"]["heal"]  # 累计治愈
        dead = pro_infos["total"]["dead"]  # 累计死亡
        mediumRiskAreaNum = pro_infos["total"]["mediumRiskAreaNum"]
        highRiskAreaNum = pro_infos["total"]["highRiskAreaNum"]
        details_province.append(
            [update_time, province, confirm, confirm_add, confirm_now, heal, dead, mediumRiskAreaNum, highRiskAreaNum,
             wzz_add])

    details_city = []
    for pro_infos in data_province:
        province = pro_infos["name"]  # 省名
        for city_infos in pro_infos["children"]:
            city = city_infos["name"]  # 城市名
            confirm_add = city_infos["today"]["local_confirm_add"]  # 本土新增确诊
            wzz_add = city_infos["today"]["wzz_add"]  # 本土无症状
            if wzz_add == '' or len(wzz_add) == 0:
                wzz_add = 0
            mediumRiskAreaNum = city_infos["total"]["mediumRiskAreaNum"]  # 中风险地区
            highRiskAreaNum = city_infos["total"]["highRiskAreaNum"]  # 高风险地区
            details_city.append(
                [update_time, province, city, confirm_add, int(wzz_add), mediumRiskAreaNum, highRiskAreaNum])
    return details_province, details_city


def get_tencent_data():
    """
    :return: 返回历史数据和当日详细数据
    """
    url_det = 'https://api.inews.qq.com/newsqa/v1/query/inner/publish/modules/list?modules=diseaseh5Shelf'
    url_his = "https://api.inews.qq.com/newsqa/v1/query/inner/publish/modules/list?modules=chinaDayList,chinaDayAddList,nowConfirmStatis,provinceCompare"

    headers = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) '
                      'Chrome/79.0.3945.88 Safari/537.36',
    }
    r_det = requests.get(url_det, headers)
    r_his = requests.get(url_his, headers)
    res_det = json.loads(r_det.text)  # json字符串转字典
    res_his = json.loads(r_his.text)
    data_det = res_det['data']['diseaseh5Shelf']
    data_his = res_his['data']

    history = {}  # 历史数据
    for i in data_his["chinaDayList"]:
        ds = i["y"] + "." + i["date"]
        tup = time.strptime(ds, "%Y.%m.%d")
        ds = time.strftime("%Y-%m-%d", tup)  # 改变时间格式,不然插入数据库会报错，数据库是datetime类型
        confirm = i["confirm"]
        confirm_now = i["nowConfirm"]
        suspect = i["suspect"]
        heal = i["heal"]
        dead = i["dead"]
        history[ds] = {"confirm": confirm, "confirm_now": confirm_now, "suspect": suspect, "heal": heal, "dead": dead}
    for i in data_his["chinaDayAddList"]:
        ds = i["y"] + "." + i["date"]
        tup = time.strptime(ds, "%Y.%m.%d")
        ds = time.strftime("%Y-%m-%d", tup)
        confirm_add = i["confirm"]
        suspect_add = i["suspect"]
        heal_add = i["heal"]
        dead_add = i["dead"]
        history[ds].update(
            {"confirm_add": confirm_add, "suspect_add": suspect_add, "heal_add": heal_add, "dead_add": dead_add})

    details = []  # 当日详细数据
    update_time = data_det["lastUpdateTime"]
    data_country = data_det["areaTree"]  # list 之前有25个国家,现在只有中国
    data_province = data_country[0]["children"]  # 中国各省
    for pro_infos in data_province:
        province = pro_infos["name"]  # 省名
        for city_infos in pro_infos["children"]:
            city = city_infos["name"]  # 城市名
            confirm = city_infos["total"]["confirm"]  # l累计确诊
            confirm_add = city_infos["today"]["confirm"]  # 新增确诊
            confirm_now = city_infos["total"]["nowConfirm"]  # 现有确诊
            heal = city_infos["total"]["heal"]  # 累计治愈
            dead = city_infos["total"]["dead"]  # 累计死亡
            details.append([update_time, province, city, confirm, confirm_add, confirm_now, heal, dead])

    return history, details


def get_baidu_hot():
    """
    :return: 返回百度疫情热搜
    """
    # url = "https://voice.baidu.com/act/virussearch/virussearch?from=osari_map&tab=0&infomore=1"
    url = "https://top.baidu.com/board?tab=realtime"
    headers = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.88 Safari/537.36',
    }
    res = requests.get(url, headers=headers)
    # res.encoding = "gb2312"
    html = res.text
    soup = BeautifulSoup(html, features="html.parser")
    kw = soup.select("div.c-single-text-ellipsis")
    count = soup.select("div.hot-index_1Bl1a")
    context = []
    for i in range(len(kw)):
        k = kw[i].text.strip()  # 移除左右空格
        v = count[i].text.strip()
        #         print(f"{k}{v}".replace('\n',''))
        context.append(f"{k}{v}".replace('\n', ''))
    return context


def update_world_details():
    """
    更新全球疫情历史数据
    :return:
    """
    cursor = None
    conn = None
    try:
        dic = get_world_data()
        print(f"{time.asctime()}开始更新海外疫情数据")
        conn, cursor = get_conn()
        sql = "insert into world values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = "select %s=(select dt from world order by id desc limit 1)"  # 对比当前最大时间戳
        cursor.execute(sql_query, dic['美国']['dt'])
        request = cursor.fetchone()[0]
        if request is None or request == 0:
            print(f"{time.asctime()}开始插入世界数据")
            for k, v in dic.items():  # item 格式 {'2021-01-13': {'confirm': 41, 'suspect': 0, 'heal': 0, 'dead': 1}
                cursor.execute(sql, [0, v.get('dt'),
                                     k, v.get("continent"),
                                     v.get("nowConfirm"),
                                     v.get("confirm"),
                                     v.get("confirmAdd"),
                                     v.get("suspect"),
                                     v.get("heal"),
                                     v.get("dead"),
                                     v.get("confirmAddCut"),
                                     v.get("confirmCompare"),
                                     v.get("nowConfirmCompare"),
                                     v.get("healCompare"),
                                     v.get("deadCompare")])
            conn.commit()  # 提交事务
            print(f"{time.asctime()}插入世界数据完毕")
        else:
            print(f"{time.asctime()}已是最新数据！")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


def update_details():
    """
    更新 details 表
    :return:
    """
    cursor = None
    conn = None
    try:
        li = get_tencent_data()[1]  # 0 是历史数据字典,1 最新详细数据列表
        conn, cursor = get_conn()
        sql = "insert into details(update_time,province,city,confirm,confirm_add,confirm_now,heal,dead) " \
              "values(%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = 'select %s=(select update_time from details order by id desc limit 1)'  # 对比当前最大时间戳
        cursor.execute(sql_query, li[0][0])
        if not cursor.fetchone()[0]:
            print(f"{time.asctime()}开始更新最新数据")
            for item in li:
                cursor.execute(sql, item)
            conn.commit()  # 提交事务 update delete insert操作
            print(f"{time.asctime()}更新最新数据完毕")
        else:
            print(f"{time.asctime()}已是最新数据！")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


def update_history():
    """
    更新历史数据
    :return:
    """
    cursor = None
    conn = None
    try:
        dic = get_tencent_data()[0]  # 0 是历史数据字典,1 最新详细数据列表
        print(f"{time.asctime()}开始更新历史数据")
        conn, cursor = get_conn()
        sql = "insert into history values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = "select confirm from history where ds=%s"
        for k, v in dic.items():
            # item 格式 {'2020-01-13': {'confirm': 41, 'suspect': 0, 'heal': 0, 'dead': 1}
            if not cursor.execute(sql_query, k):  # 如果当天数据不存在，才写入
                cursor.execute(sql, [k, v.get("confirm"), v.get("confirm_add"), v.get("confirm_now"),
                                     v.get("suspect"), v.get("suspect_add"), v.get("heal"),
                                     v.get("heal_add"), v.get("dead"), v.get("dead_add")])
        conn.commit()  # 提交事务 update delete insert操作
        print(f"{time.asctime()}历史数据更新完毕")
    except:
        traceback.print_exc()
    finally:
        conn.close()
        cursor.close()
        # close_conn(conn, cursor)


def update_hotsearch():
    """
    将疫情热搜插入数据库
    :return:
    """
    cursor = None
    conn = None
    try:
        context = get_baidu_hot()
        print(f"{time.asctime()}开始更新热搜数据")
        conn, cursor = get_conn()
        sql = "insert into hotsearch(dt,content) values(%s,%s)"
        ts = time.strftime("%Y-%m-%d %X")
        for i in context:
            cursor.execute(sql, (ts, i))  # 插入数据
        conn.commit()  # 提交事务保存数据
        print(f"{time.asctime()}数据更新完毕")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


def update_details_province():
    """
    更新 details 表
    :return:
    """
    cursor = None
    conn = None
    try:
        li = get_map_data()[0]  # 0 是省级相关数据,1 是市级相关数据
        conn, cursor = get_conn()
        sql = "insert into details_province(update_time, province, confirm, confirm_add, confirm_now, heal, dead, mediumRiskAreaNum, highRiskAreaNum,wzz_add) " \
              "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = 'select %s=(select update_time from details_province order by id desc limit 1)'  # 对比当前最大时间戳
        cursor.execute(sql_query, li[0][0])
        if not cursor.fetchone()[0]:
            print(f"{time.asctime()}开始更新最新数据")
            for item in li:
                cursor.execute(sql, item)
            conn.commit()  # 提交事务 update delete insert操作
            print(f"{time.asctime()}更新最新数据完毕")
        else:
            print(f"{time.asctime()}已是最新数据！")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


def update_details_city():
    """
    更新 details 表
    :return:
    """
    cursor = None
    conn = None
    try:
        li = get_map_data()[1]  # 0 是省级相关数据,1 是市级相关数据
        conn, cursor = get_conn()
        sql = "insert into details_city(update_time,province,city,confirm_add,wzz_add,mediumRiskAreaNum,highRiskAreaNum) " \
              "values(%s,%s,%s,%s,%s,%s,%s)"
        sql_query = 'select %s=(select update_time from details_city order by id desc limit 1)'  # 对比当前最大时间戳
        cursor.execute(sql_query, li[0][0])
        if not cursor.fetchone()[0]:
            print(f"{time.asctime()}开始更新最新数据")
            for item in li:
                cursor.execute(sql, item)
            conn.commit()  # 提交事务 update delete insert操作
            print(f"{time.asctime()}更新最新数据完毕")
        else:
            print(f"{time.asctime()}已是最新数据！")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


def gd_map(addr):
    url = 'https://restapi.amap.com/v3/geocode/geo'  # 高德地图地理编码API服务地址
    params = {'key': 'eaa72b97e0bccf59232582c2ee2df0ff',  # 高德Key
              'address': addr}  # 地址参数

    res = requests.get(url, params)  # GET方式请求
    jd = json.loads(res.text)['geocodes'][0]['location']
    print(jd)
    return jd


if __name__ == "__main__":
    update_history()
    update_details_city()
    update_details_province()
    update_hotsearch()
    update_world_details()

    # linux下的部署
    # s = """请输入参数:
    # up_his  更新历史记录表
    # up_hot  更新实时热搜
    # up_det  更新城市详细表
    # up_world  更新全球详细表
    # """
    # l = len(sys.argv)
    # if l == 1:
    #     print(s)
    # else:
    #     order = sys.argv[1]
    #     if order == "up_his":
    #         update_history()
    #     elif order == "up_det":
    #         update_details_city()
    #         update_details_province()
    #     elif order == "up_hot":
    #         update_hotsearch()
    #     elif order == "up_world":
    #         update_world_details()
    #     else:
    #         print("错误的参数")
    #         print(s)

